package com.neusoft.hifly.commons.database;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.lang3.StringUtils;

import com.neusoft.hifly.commons.database.vo.Field;
import com.neusoft.hifly.commons.exception.SystemErrorException;
import com.neusoft.hifly.commons.id.IdWorker;
import com.neusoft.hifly.commons.lang.StringConstants;
import com.neusoft.hifly.commons.lang.StringUtil;
import com.neusoft.hifly.core.log.Log;

/**
 * Title: 核心类库
 * <p>
 * Description: 数据库表生成器
 * <p>
 * Copyright: 2019
 * <p>
 * Company: 东软汉枫
 *
 * @author lh_neu
 * @version 1.0.2
 */
public final class TableUtil {

	private static TableUtil instance;

	/**
	 * 默认构造函数
	 */
	private TableUtil() {
	}

	/**
	 * 获取单例对象
	 *
	 * @return 对象
	 */
	public synchronized static TableUtil getInstanse() {
		if (TableUtil.instance == null) {
			TableUtil.instance = new TableUtil();
		}
		return TableUtil.instance;
	}

	/**
	 * 生成名称，首字母小写，后词组首字母大写
	 *
	 * @param objName
	 *            字段名
	 * @return 属性名
	 */
	public static String columnToField(String objName) {
		// 全部转换为小写
		objName = StringUtils.trimToEmpty(StringUtils.lowerCase(objName));

		final String[] split = StringUtil.str2Arr(objName, "_");
		final StringBuffer objNameStr = new StringBuffer("");
		if ((split != null) && (split.length >= 1)) {
			for (final String string : split) {
				// 首字母大写
				objNameStr.append(StringUtils.capitalize(string));
			}
		}
		return StringUtils.uncapitalize(objNameStr.toString());
	}

	/**
	 * 获取查询语句中SELECT与FROM中待显示的列
	 *
	 * @param sql
	 *            SQL语句
	 * @param isNegated
	 *            true：别名当键，列名当值，false：列名当键，列名别名
	 * @return 有序的列
	 */
	public ListOrderedMap splitFields(String sql, final boolean isNegated) {
		if (StringUtils.isEmpty(sql)) {
			return null;
		}
		// 去掉换行
		sql = sql.replaceAll("\r\t", " ");
		sql = StringUtils.substringBetween(StringUtils.lowerCase(sql), "select", "from");
		if (StringUtils.isEmpty(sql)) {
			return null;
		}

		/* 去掉CASE 和 END 之间的东西 */
		sql = StringUtil.replace(sql, "CASE ", " END", true, "CASEEND");

		// 分隔每个列
		final String[] arr = StringUtil.str2Arr(sql);
		ListOrderedMap fieldMap = null;
		if (arr != null && arr.length >= 1) {
			fieldMap = new ListOrderedMap();
			// 数据库列
			String key = "";
			// 表达式中的别名
			String value = "";
			for (String string : arr) {
				// 去掉空格
				string = StringUtils.trim(string);
				// 两种方式去掉别名：“model.USER_NAME 用户名” 或者  “model.USER_NAME as 用户名”
				if (StringUtils.lowerCase(string).indexOf(" as ") >= 0) {
					key = StringUtils.trim(StringUtils.substringBefore(string, "as"));
					value = StringUtils.trim(StringUtils.substringAfterLast(string, "as"));
				} else {
					key = StringUtils.trim(StringUtils.substringBefore(string, " "));
					value = StringUtils.trim(StringUtils.substringAfterLast(string, " "));
				}
				// 去掉key中表的别名：model.
				if (StringUtils.isNotEmpty(key) && key.indexOf(".") >= 0) {
					key = StringUtils.trim(StringUtils.substringAfterLast(key, "."));
				}

				if (!isNegated) {
					// 别名当键，列名当值
					if (StringUtils.isNotEmpty(key)) {
						fieldMap.put(key, value);
					}
				} else {
					// 别名当键，列名别名
					if (StringUtils.isNotEmpty(value)) {
						fieldMap.put(value, key);
					}
				}

			}
		}
		return fieldMap;
	}

	/**
	 * 获取查询语句中SELECT与FROM中待显示的列，带别名，为了搜索时使用 WuGang 2013-10-09
	 *
	 * @param sql
	 *            SQL语句
	 * @param isNegated
	 *            true：别名当键，列名当值，false：列名当键，列名别名
	 * @return 有序的列
	 */
	public ListOrderedMap splitFilterFields(String sql, final boolean isNegated) {
		if (StringUtils.isEmpty(sql)) {
			return null;
		}
		// 去掉换行
		sql = sql.replaceAll("\r\t", " ");
		sql = StringUtils.substringBetween(StringUtils.lowerCase(sql), "select", "from");
		if (StringUtils.isEmpty(sql)) {
			return null;
		}

		/* 去掉CASE 和 END 之间的东西 */
		sql = StringUtil.replace(sql, "CASE", "END", true, "CASEEND");

		// 分隔每个列
		final String[] arr = StringUtil.str2Arr(sql);
		ListOrderedMap fieldMap = null;
		if (arr != null && arr.length >= 1) {
			fieldMap = new ListOrderedMap();
			// 数据库列
			String key = "";
			// 表达式中的别名
			String value = "";
			for (String string : arr) {
				// 去掉空格
				string = StringUtils.trim(string);
				// 两种方式去掉别名：“model.USER_NAME 用户名” 或者  “model.USER_NAME as 用户名”
				if (StringUtils.lowerCase(string).indexOf(" as ") >= 0) {
					key = StringUtils.trim(StringUtils.substringBefore(string, "as"));
					value = StringUtils.trim(StringUtils.substringAfterLast(string, "as"));
				} else {
					key = StringUtils.trim(StringUtils.substringBefore(string, " "));
					value = StringUtils.trim(StringUtils.substringAfterLast(string, " "));
				}
				// 去掉key中表的别名：model.
				//if (StringUtils.isNotEmpty(key) && key.indexOf(".") >= 0) {
				//	key = StringUtils.trim(StringUtils.substringAfterLast(key, "."));
				//}

				if (!isNegated) {
					// 别名当键，列名当值
					if (StringUtils.isNotEmpty(key)) {
						fieldMap.put(key, value);
					}
				} else {
					// 别名当键，列名别名
					if (StringUtils.isNotEmpty(value)) {
						fieldMap.put(value, key);
					}
				}

			}
		}
		return fieldMap;
	}

	/**
	 * 添加参数
	 * <p>
	 * 根据不同的数据类型添加方法不一样
	 *
	 * @param field
	 *            数据列
	 * @param dataType
	 *            数据类型
	 * @param value
	 *            数据值
	 * @return 拼接好的参数
	 */
	public String addParam(final String field, final String dataType, final Object value) {
		if (StringUtils.isEmpty(field)) {
			return "";
		}
		final StringBuffer sql = new StringBuffer(field + "=");
		if (StringUtils.isNotEmpty(dataType) && (dataType.equalsIgnoreCase("int") || dataType.equalsIgnoreCase("long")
				|| dataType.equalsIgnoreCase("number") || dataType.equalsIgnoreCase("integer"))) {
			sql.append(value);
		} else {
			if (value == null) {
				sql.append("''");
			} else {
				sql.append("'" + SQLUtil.replaceStr(value.toString()) + "'");
			}
		}
		return sql.toString();
	}

	/**
	 * 查询所有表名
	 * <p>
	 * 查询表名tableName、表注释tableComment（相当于表的中文名）
	 *
	 * @param type
	 *            数据量类型
	 * @param beforeName
	 *            表的前缀名 本系统默认为 "OA_"
	 * @param execptName
	 *            去除查询范围的表前缀/后缀名 本系统默认为 "OA_TABLE"
	 * @param schema
	 *            数据库名，mysql用
	 * @return 语句
	 */
	public String generateAllTableNamesDDL(final String type, String beforeName, final String execptName,
			final String schema) {
		if (SQLUtil.DB_ORACLE.equalsIgnoreCase(type)) {
			return allTableNamesDDLByOracle(beforeName, execptName);
		} else if (SQLUtil.DB_EDB.equalsIgnoreCase(type)) {
			return allTableNamesDDLByOracle(beforeName, execptName);
		} else if (SQLUtil.DB_MYSQL.equalsIgnoreCase(type)) {
			if (StringUtils.isNotEmpty(beforeName)) {
				// 若有“_”的，必须替换成“\_”，否则“_”会被like语句视为通配符
				beforeName = beforeName.replaceAll("_", "\\\\_");
			}
			return allTableNamesDDLByMySQL(schema, beforeName, execptName);
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(type)) {
			return allTableNamesDDLByMySQL(schema, beforeName, execptName);
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(type)) {
			return null;
		} else if (SQLUtil.DB_SYBASE.equalsIgnoreCase(type)) {
			return allTableNamesDDLByOracle(beforeName, execptName);
		} else if (SQLUtil.DB_DB2.equalsIgnoreCase(type)) {
			return allTableNamesDDLByOracle(beforeName, execptName);
		}
		return "";
	}

	/**
	 * 通过表名查询该表下所有的字段
	 * <p>
	 * 英文列名（字段名）columnName、列注释columnComment、列类型dataType、 列大小dataLength
	 * <p>
	 * 中文列名（默认情况下中文名=列注释，若列注释为空，则默认为英文列名）
	 *
	 * @param type
	 *            数据量类型
	 * @param tableName
	 *            表名
	 * @param schema
	 *            数据库名，mysql用
	 * @return 语句
	 */
	public String generateAllFieldsDDL(final String type, final String tableName, final String schema) {
		if (StringUtils.isEmpty(tableName)) {
			return "";
		}
		if (SQLUtil.DB_ORACLE.equalsIgnoreCase(type)) {
			return allFieldsDDLByOracle(tableName);
		} else if (SQLUtil.DB_EDB.equalsIgnoreCase(type)) {
			return allFieldsDDLByOracle(tableName);
		} else if (SQLUtil.DB_MYSQL.equalsIgnoreCase(type)) {
			return allFieldsDDLByMySQL(schema, tableName);
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(type)) {
			return allFieldsDDLByMySQL(schema, tableName);
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(type)) {
			return null;
		} else if (SQLUtil.DB_SYBASE.equalsIgnoreCase(type)) {
			return allFieldsDDLByOracle(tableName);
		} else if (SQLUtil.DB_DB2.equalsIgnoreCase(type)) {
			return allFieldsDDLByOracle(tableName);
		}
		return "";
	}

	/**
	 * 随机生成数据库表名称
	 *
	 * @author lh_neu
	 * @date 2013-12-3 上午10:03:51
	 * @param prefix
	 *            表名前缀
	 * @param postfix
	 *            表名后缀
	 * @return 表名
	 */
	public synchronized String getRandomTableName(final String prefix, final String postfix) {
		final IdWorker idWorker = new IdWorker(new Random().nextInt(30) + 1, 1);
		final StringBuffer tableName = new StringBuffer(idWorker.nextId() + "");
		if (StringUtils.isNotEmpty(prefix)) {
			tableName.insert(0, prefix + "_");
		}
		if (StringUtils.isNotEmpty(postfix)) {
			tableName.append("_" + postfix);
		}
		return tableName.toString();
	}

	/**
	 * 修改数据库表空间
	 *
	 * @param type
	 *            数据量类型
	 * @param oldTableSpaceName
	 *            原表空间名
	 * @param newTableSpaceName
	 *            新表空间名
	 * @return 语句
	 */
	public String generateRenameTableSpaceNameDDL(final String type, final String oldTableSpaceName,
			final String newTableSpaceName) {
		if (StringUtils.isEmpty(oldTableSpaceName) || StringUtils.isEmpty(newTableSpaceName)) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");
		sql.append("ALTER TABLESPACE " + oldTableSpaceName + " RENAME TO " + newTableSpaceName + ";");
		sql.append(StringConstants.TAG_R_N);

		return sql.toString();
	}

	/**
	 * 创建表
	 *
	 * @param type
	 *            数据量类型
	 * @param tableName
	 *            表名
	 * @param tableComment
	 *            表注释
	 * @param fields
	 *            列
	 * @return 语句
	 */
	public String generateTableDDL(final String type, final String tableName, final String tableComment,
			final List<Field> fields) {
		if (StringUtils.isEmpty(tableName) || fields == null || fields.isEmpty()) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");
		if (SQLUtil.DB_ORACLE.equalsIgnoreCase(type)) {
			tableDDLByOracle(tableName, tableComment, fields, sql);
		} else if (SQLUtil.DB_EDB.equalsIgnoreCase(type)) {
			tableDDLByOracle(tableName, tableComment, fields, sql);
		} else if (SQLUtil.DB_MYSQL.equalsIgnoreCase(type)) {
			tableDDLByMySQL(tableName, tableComment, fields, sql);
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(type)) {
			tableDDLByMySQL(tableName, tableComment, fields, sql);
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(type)) {
			tableDDLBySQLServer(tableName, tableComment, fields, sql);
		} else if (SQLUtil.DB_SYBASE.equalsIgnoreCase(type)) {
			tableDDLByOracle(tableName, tableComment, fields, sql);
		} else if (SQLUtil.DB_DB2.equalsIgnoreCase(type)) {
			tableDDLByOracle(tableName, tableComment, fields, sql);
		}

		return sql.toString();
	}

	/**
	 * 修改数据库表名
	 *
	 * @param type
	 *            数据量类型
	 * @param oldTableName
	 *            原表名
	 * @param newTableName
	 *            新表名
	 * @return 语句
	 */
	public String generateRenameTableDDL(final String type, final String oldTableName, final String newTableName) {
		if (StringUtils.isEmpty(oldTableName) || StringUtils.isEmpty(newTableName)) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");
		sql.append("ALTER TABLE " + oldTableName + " RENAME TO " + newTableName + ";");
		sql.append(StringConstants.TAG_R_N);

		return sql.toString();
	}

	/**
	 * 删除数据库表
	 *
	 * @param type
	 *            数据量类型
	 * @param oldTableName
	 *            原表名
	 * @param newTableName
	 *            新表名
	 * @return 语句
	 */
	public String generateDropTableDDL(final String type, final String tableName) {
		if (StringUtils.isEmpty(tableName)) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");

		if (SQLUtil.DB_MYSQL.equalsIgnoreCase(type)) {
			sql.append(" DROP TABLE IF EXISTS " + tableName + "; ");
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(type)) {
			sql.append(" DROP TABLE IF EXISTS " + tableName + "; ");
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(type)) {
			sql.append("IF EXISTS (SELECT 1 " + StringConstants.TAG_R_N);
			sql.append("          FROM  SYSOBJECTS " + StringConstants.TAG_R_N);
			sql.append("        WHERE  ID = OBJECT_ID('" + tableName + "') " + StringConstants.TAG_R_N);
			sql.append("         AND   TYPE = 'U') " + StringConstants.TAG_R_N);
			sql.append("DROP TABLE " + tableName + ") " + StringConstants.TAG_R_N);
			sql.append("GO) " + StringConstants.TAG_R_N);
		} else {
			// Oracle
			sql.append(" DROP TABLE " + tableName + " CASCADE CONSTRAINTS; ");
		}
		return sql.toString();
	}

	/**
	 * 修改字段名称
	 *
	 * @param type
	 *            数据量类型
	 * @param tableName
	 *            表名
	 * @param oldFieldName
	 *            原字段名称
	 * @param newFieldName
	 *            新字段名称
	 * @return 语句
	 */
	public String generateRenameFieldDDL(final String type, final String tableName, final String oldFieldName,
			final String newFieldName) {
		if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(oldFieldName) || StringUtils.isEmpty(newFieldName)) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");
		sql.append("ALTER TABLE " + tableName + " RENAME FIELD " + oldFieldName + " TO " + newFieldName + ";");
		sql.append(StringConstants.TAG_R_N);

		return sql.toString();
	}

	/**
	 * 移动表到另一个表空间
	 *
	 * @param type
	 *            数据量类型
	 * @param tableName
	 *            表名
	 * @param newTableSpaceName
	 *            新表空间名
	 * @return 语句
	 */
	public String generateChangeTableToOtherSpaceDDL(final String type, final String tableName,
			final String newTableSpaceName) {
		if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(newTableSpaceName)) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");
		sql.append("ALTER TABLE " + tableName + " SET TABLESPACE " + newTableSpaceName + ";");
		sql.append(StringConstants.TAG_R_N);

		return sql.toString();
	}

	/**
	 * 修改表：创建主键
	 *
	 * @param type
	 *            数据库类型表名
	 * @param tableName
	 *            表名
	 * @param key
	 *            主键
	 * @return 语句
	 */
	public String generateAlertAddPrimaryDDL(final String type, final String tableName, final String key) {
		if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(key)) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");
		sql.append("ALTER TABLE " + tableName + " ADD PRIMARY KEY (" + key + ");");
		sql.append(StringConstants.TAG_R_N);

		return sql.toString();
	}

	/**
	 * 修改表：添加唯一约束
	 *
	 * @param type
	 *            数据库类型
	 * @param tableName
	 *            表名
	 * @param constraintName
	 *            约束名称:U_NAME
	 * @param uniqueFields
	 *            约束列（单列或多列）
	 * @return 语句
	 */
	public String generateAlertAddUniqueDDL(final String type, final String tableName, final String constraintName,
			final String[] uniqueFields) {
		if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(constraintName) || uniqueFields == null
				|| uniqueFields.length == 0) {
			return "";
		}
		final StringBuffer unique = new StringBuffer("");
		for (final String field : uniqueFields) {
			unique.append(field + StringConstants.STRING_SPLIT_FLAG_3);
		}
		unique.delete(unique.length() - 1, unique.length());
		final StringBuffer sql = new StringBuffer("");
		sql.append("ALTER TABLE " + tableName + " ADD CONSTRAINT " + constraintName + " UNIQUE (" + unique.toString()
				+ ");");
		sql.append(StringConstants.TAG_R_N);

		return sql.toString();
	}

	/**
	 * 修改表：添加字段
	 *
	 * @param type
	 *            数据库类型
	 * @param tableName
	 *            表名
	 * @param fields
	 *            列
	 * @return 语句
	 */
	public String generateAlertAddFieldDDL(final String type, final String tableName, final List<Field> fields) {
		if (StringUtils.isEmpty(tableName) || fields == null || fields.isEmpty()) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");
		if (SQLUtil.DB_ORACLE.equalsIgnoreCase(type)) {
			alertAddFieldByOracle(tableName, fields, sql);
		} else if (SQLUtil.DB_EDB.equalsIgnoreCase(type)) {
			alertAddFieldByOracle(tableName, fields, sql);
		} else if (SQLUtil.DB_MYSQL.equalsIgnoreCase(type)) {
			alertAddFieldByMySQL(tableName, fields, sql);
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(type)) {
			alertAddFieldByMySQL(tableName, fields, sql);
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(type)) {
			alertAddFieldBySQLServer(tableName, fields, sql);
		} else if (SQLUtil.DB_SYBASE.equalsIgnoreCase(type)) {
			alertAddFieldByOracle(tableName, fields, sql);
		} else if (SQLUtil.DB_DB2.equalsIgnoreCase(type)) {
			alertAddFieldByOracle(tableName, fields, sql);
		}

		return sql.toString();
	}

	/**
	 * 修改表：修改字段
	 *
	 * @param type
	 *            数据库类型
	 * @param tableName
	 *            表名
	 * @param fields
	 *            列
	 * @return 语句
	 * @return
	 */
	public String generateAlertModifyFieldDDL(final String type, final String tableName, final List<Field> fields) {
		if (StringUtils.isEmpty(tableName) || fields == null || fields.isEmpty()) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");
		if (SQLUtil.DB_ORACLE.equalsIgnoreCase(type)) {
			alertModifyFieldByOracle(tableName, fields, sql);
		} else if (SQLUtil.DB_EDB.equalsIgnoreCase(type)) {
			alertModifyFieldByOracle(tableName, fields, sql);
		} else if (SQLUtil.DB_MYSQL.equalsIgnoreCase(type)) {
			alertModifyFieldByMySQL(tableName, fields, sql);
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(type)) {
			alertModifyFieldByMySQL(tableName, fields, sql);
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(type)) {
			alertModifyFieldBySQLServer(tableName, fields, sql);
		} else if (SQLUtil.DB_SYBASE.equalsIgnoreCase(type)) {
			alertModifyFieldByOracle(tableName, fields, sql);
		} else if (SQLUtil.DB_DB2.equalsIgnoreCase(type)) {
			alertModifyFieldByOracle(tableName, fields, sql);
		}

		return sql.toString();
	}

	/**
	 * 修改表：删除字段
	 *
	 * @param type
	 *            数据库类型
	 * @param tableName
	 *            表名
	 * @param fields
	 *            列
	 * @return 语句
	 */
	public String generateAlertDropFieldDDL(final String type, final String tableName, final List<Field> fields) {
		if (StringUtils.isEmpty(tableName) || fields == null || fields.isEmpty()) {
			return "";
		}
		final StringBuffer sql = new StringBuffer("");

		for (final Field field : fields) {
			sql.append("ALTER TABLE " + tableName + " DROP COLUMN " + field.getDataCode() + ";");
			sql.append(StringConstants.TAG_R_N);
		}

		return sql.toString();
	}

	/**
	 * Executes a SQL script.
	 * <p>
	 * 若想留住;，可在SQL文件中使用“\;”，在这里直接过滤了
	 *
	 * @param conn
	 *            database connection.
	 * @param resourceName
	 *            an input stream for the script to execute.
	 * @param encode
	 *            字符集，默认字符集GBK
	 * @param replaceSqlMap
	 *            automatically replace sql with key.
	 * @param ignore
	 *            是否忽略错误
	 * @return 执行的SQL语句
	 * @exception SystemErrorException
	 *                Excetion
	 */
	public String executeSQLScript(final Connection conn, final String resourceName, final String encode,
			final Map<String, String> replaceSqlMap, final boolean ignore) throws SystemErrorException {
		return this.executeSQLScript(conn, resourceName, SQLUtil.getDbType(), encode, replaceSqlMap, true, ignore);
	}

	/**
	 * Executes a SQL script.
	 * <p>
	 * 若想留住;，可在SQL文件中使用“\;”，在这里直接过滤了
	 *
	 * @param conn
	 *            database connection.
	 * @param resourceName
	 *            an input stream for the script to execute.
	 * @param dbType
	 *            数据库类型
	 * @param encode
	 *            字符集，默认字符集GBK
	 * @param replaceSqlMap
	 *            automatically replace sql with key.
	 * @param isExecute
	 *            是否执行语句，若不实现，则可以直接返回待执行的SQL语句
	 * @param ignore
	 *            是否忽略错误
	 * @return 执行的SQL语句
	 * @exception SystemErrorException
	 *                Excetion
	 */
	public String executeSQLScript(final Connection conn, final String resourceName, final String dbType, String encode,
			final Map<String, String> replaceSqlMap, final boolean isExecute, final boolean ignore)
			throws SystemErrorException {

		if ((isExecute && conn == null) || StringUtils.isEmpty(resourceName)) {
			return "";
		}

		if (StringUtils.isEmpty(encode)) {
			encode = "GBK";
		}

		// 读取资源文件流
		final File file = new File(resourceName);
		if (!file.exists()) {
			Log.error("未找到资源文件 [ " + resourceName + " ] !");
		}

		InputStream resource = null;
		try {
			resource = new FileInputStream(file);
		} catch (final FileNotFoundException e) {
			Log.error("未找到资源文件 [ " + resourceName + " ] !");
		}
		if (resource == null) {
			return "";
		}

		BufferedReader in = null;
		PreparedStatement pstmt = null;
		String cmdString = null;
		Map.Entry<String, String> entry = null;
		final StringBuffer error = new StringBuffer("");
		int currentLine = 0;
		final StringBuffer result = new StringBuffer("");
		try {
			final InputStreamReader read = new InputStreamReader(resource, encode);
			in = new BufferedReader(read);
			boolean done = false;
			boolean isContinue = false;
			final StringBuffer command = new StringBuffer("");
			String line = null;
			while (!done) {
				// 拼接SQL语句，以；为结束符
				command.setLength(0); // 清空对象
				while (true) {
					line = in.readLine();
					currentLine++;
					if (line == null) {
						done = true;
						break;
					}
					// sqlserver用go做分行标记，遇到标示一句SQL结束
					if ("go".equalsIgnoreCase(line.trim())) {
						break;
					}
					// Ignore comments and blank lines.
					if (isSQLCommandPart(line)) {
						// 若想留住;，可在SQL文件中使用“\;”，在这里直接过滤了
						if (line.trim().endsWith("\\;")) {
							line = StringUtils.substringBeforeLast(line, "\\;") + ";";
							isContinue = true;
						}
						command.append(" ").append(line);
					}

					if (isContinue) {
						isContinue = false;
						continue;
					}

					// 用;标记结束
					if (line.trim().endsWith(";")) {
						break;
					}
				}
				// Send command to database.
				if (!done && !command.toString().equals("")) {
					// Remove last semicolon when using Oracle or DB2 to prevent "invalid character error"
					if (isExecute && SQLUtil.DB_ORACLE.equalsIgnoreCase(dbType)
							|| SQLUtil.DB_DB2.equalsIgnoreCase(dbType)) {
						command.deleteCharAt(command.length() - 1);
					}
					try {
						cmdString = StringUtils.trimToEmpty(command.toString());
						if (StringUtils.isEmpty(cmdString)) {
							continue;
						}
						// 替换sql语句
						if (replaceSqlMap != null && !replaceSqlMap.isEmpty()) {
							final Iterator<Map.Entry<String, String>> iter = replaceSqlMap.entrySet().iterator();
							while (iter.hasNext()) {
								entry = iter.next();
								cmdString = cmdString.replaceAll(entry.getKey(), entry.getValue());
							}
						}

						if (isExecute) {
							pstmt = conn.prepareStatement(cmdString);
							pstmt.execute();
						} else {
							result.append(cmdString + "\n");
						}
					} catch (final SQLException e) {
						if (ignore) {
							error.append(cmdString + "\n" + e.getMessage() + "\n");
						} else {
							// Lets show what failed
							Log.error("SchemaManager: Failed to execute SQL:\n" + cmdString);
							throw new SystemErrorException("执行SQL语句失败:\n 第[ " + currentLine + " ]行：" + cmdString);
						}
					} finally {
						if (isExecute && pstmt != null) {
							try {
								pstmt.close();
							} catch (final Exception e) {
								Log.error(e.getMessage(), e);
							}
						}
					}
				}
				line = null;
			}
			if (StringUtils.isNotEmpty(error.toString())) {
				throw new SystemErrorException("以下SQL语句执行失败:\n " + error.toString());
			}

		} catch (final IOException e) {
			Log.error(e.getMessage());
			throw new SystemErrorException(" [ " + resourceName + " ] !");
		} finally {
			try {
				if (in != null) {
					in.close();
				}
				if (resource != null) {
					resource.close();
				}
				if (conn != null) {
					if (!conn.isClosed()) {
						conn.close();
					}
				}
			} catch (final Exception e) {
				Log.error(e.getMessage(), e);
			}

		}
		return result.toString();
	}

	/**
	 * 查询所有表名
	 * <p>
	 * 查询表名tableName、表注释tableComment（相当于表的中文名）
	 *
	 * @param beforeName
	 *            表的前缀名 本系统默认为 "OA_"
	 * @param execptName
	 *            去除查询范围的表前缀名 本系统默认为 "OA_TABLE"
	 * @return 语句
	 */
	private String allTableNamesDDLByOracle(final String beforeName, final String execptName) {
		final StringBuffer sql = new StringBuffer(
				"SELECT TABLE_NAME tableName,COMMENTS tableComment FROM USER_TAB_COMMENTS WHERE UPPER(TABLE_NAME) LIKE '"
						+ beforeName.toUpperCase() + "%' ");
		if (StringUtils.isNotEmpty(execptName)) {
			sql.append(" AND UPPER(TABLE_NAME) NOT LIKE '" + execptName.toUpperCase() + "%' ");
			sql.append(" AND UPPER(TABLE_NAME) NOT LIKE '%" + execptName.toUpperCase() + "' ");
		}

		return sql.toString();
	}

	/**
	 * 查询所有表名
	 * <p>
	 * 查询表名tableName、表注释tableComment（相当于表的中文名）
	 *
	 * @param schema
	 *            数据库名
	 * @param beforeName
	 *            表的前缀名 本系统默认为 "OA_"
	 * @param execptName
	 *            去除查询范围的表前缀名 本系统默认为 "OA_TABLE"
	 * @return 语句
	 */
	private String allTableNamesDDLByMySQL(final String schema, final String beforeName, final String execptName) {
		final StringBuffer sql = new StringBuffer(
				"SELECT TABLE_NAME tableName,TABLE_COMMENT tableComment FROM INFORMATION_SCHEMA.TABLES WHERE UPPER(TABLE_NAME) LIKE '"
						+ beforeName.toUpperCase() + "%' ");
		if (StringUtils.isNotEmpty(execptName)) {
			sql.append(" AND UPPER(TABLE_NAME) NOT LIKE '" + execptName.toUpperCase() + "%' ");
			sql.append(" AND UPPER(TABLE_NAME) NOT LIKE '%" + execptName.toUpperCase() + "' ");
		}
		if (StringUtils.isNotEmpty(schema)) {
			sql.append(" AND TABLE_SCHEMA = '" + SQLUtil.replaceStr(schema) + "' ");
		}

		return sql.toString();
	}

	/**
	 * 通过表名查询该表下所有的字段
	 *
	 * @param tableName
	 *            表名
	 * @return 语句
	 */
	private String allFieldsDDLByOracle(final String tableName) {
		final StringBuffer sql = new StringBuffer("SELECT cl.table_name tablename, cl.column_name columnName, "
				+ " cl.data_type dataType, cl.data_length dataLength, incm.columnComment columnComment "
				+ " FROM user_tab_columns cl, " + " (SELECT cm.column_name columnName, cm.comments columnComment "
				+ " FROM user_col_comments cm  WHERE UPPER(cm.table_name) = '" + SQLUtil.replaceStr(tableName)
				+ "') incm " + " WHERE incm.columnName = cl.column_name AND UPPER(cl.table_name) = '"
				+ SQLUtil.replaceStr(tableName) + "' ");

		return sql.toString();
	}

	/**
	 * 通过表名查询该表下所有的字段
	 *
	 * @param schema
	 *            数据库名，mysql用
	 * @param tableName
	 *            表名
	 * @return 语句
	 */
	private String allFieldsDDLByMySQL(final String schema, final String tableName) {
		final StringBuffer sql = new StringBuffer("select COLUMN_NAME columnName, COLUMN_COMMENT columnComment, "
				+ " DATA_TYPE dataType, CHARACTER_MAXIMUM_LENGTH dataLength FROM INFORMATION_SCHEMA.COLUMNS "
				+ " WHERE TABLE_NAME = '" + SQLUtil.replaceStr(tableName) + "' ");

		if (StringUtils.isNotEmpty(schema)) {
			sql.append(" AND TABLE_SCHEMA = '" + SQLUtil.replaceStr(schema) + "' ");
		}

		return sql.toString();
	}

	/**
	 * Returns true if a line from a SQL schema is a valid command part.
	 *
	 * @param line
	 *            the line of the schema.
	 * @return true if a valid command part.
	 */
	private boolean isSQLCommandPart(String line) {
		line = line.trim();
		if (line.equals("")) {
			return false;
		}
		// Check to see if the line is a comment. Valid comment types:
		//   "//" is HSQLDB
		//   "--" is Oracle、DB2 and Postgres
		//   "#" is MySQL
		//   "/*" is Oracle、SQLServer
		return !(line.startsWith("//") || line.startsWith("--") || line.startsWith("#") || line.startsWith("/*")
				|| line.startsWith("*"));
	}

	/**
	 * 基于ORACLE类型的创建表DDL
	 *
	 * @param tableName
	 *            表明
	 * @param tableComment
	 *            表注释
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void tableDDLByOracle(final String tableName, final String tableComment, final List<Field> fields,
			final StringBuffer sql) {
		//			DROP TABLE USER_INFO CASCADE CONSTRAINTS;
		//				/*===================================*/
		//				/* TABLE: USER_INFO                  */
		//				/*===================================*/
		//				CREATE TABLE USER_INFO  (
		//				   LOGIN_NAME         NVARCHAR2(100)    NOT NULL,
		//				   EXT1               NVARCHAR2(100) DEFAULT '0',
		//				   CONSTRAINT PK_USER_INFO PRIMARY KEY (LOGIN_NAME)
		//				);
		//
		//				COMMENT ON TABLE USER_INFO IS
		//				'用户信息表';
		//
		//				COMMENT ON COLUMN USER_INFO.LOGIN_NAME IS
		//				'登录名';
		//
		//				CREATE INDEX INX_USER_INFO_LOGIN_NAME ON USER_INFO (LOGIN_NAME) ;

		// 删除表
		sql.append("DROP TABLE " + tableName + " CASCADE CONSTRAINTS;" + StringConstants.TAG_R_N);
		// 表注释
		sql.append("/*===================================*/" + StringConstants.TAG_R_N);
		sql.append("/* TABLE: " + tableName + "                  */" + StringConstants.TAG_R_N);
		sql.append("/*===================================*/" + StringConstants.TAG_R_N);

		// 创建表
		sql.append("CREATE TABLE " + tableName + "  (" + StringConstants.TAG_R_N);
		// 主键
		Field primaryKey = null;
		// 列注释
		final StringBuffer comment = new StringBuffer("");
		// 序列
		final StringBuffer index = new StringBuffer("");
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			if (StringConstants.YES.equalsIgnoreCase(field.getDataIsPrimaryKey())) {
				primaryKey = field;
			}
			// 拼接表属性
			sql.append(" " + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			sql.append(",");

			sql.append(StringConstants.TAG_R_N);

			comment.append("COMMENT ON COLUMN " + tableName + "." + field.getDataCode() + " IS '" + field.getDataName()
					+ "';");
			comment.append(StringConstants.TAG_R_N);

			if (StringConstants.YES.equalsIgnoreCase(field.getDataIsIndex())) {
				index.append("CREATE INDEX INX_" + tableName + "_" + field.getDataCode() + " ON " + tableName + " ("
						+ field.getDataCode() + ") ;");
				index.append(StringConstants.TAG_R_N);
			}
		}
		// 是否有主键
		if (primaryKey != null) {
			sql.append("CONSTRAINT PK_" + tableName + " PRIMARY KEY (" + primaryKey.getDataCode() + ")");
		} else {
			// 删除多余的
			sql.delete(sql.length() - ("," + StringConstants.TAG_R_N).length(), sql.length());
		}
		sql.append(StringConstants.TAG_R_N);
		sql.append(");");

		sql.append(StringConstants.TAG_R_N);
		sql.append("COMMENT ON TABLE " + tableName + " IS '" + tableComment + "';");
		sql.append(StringConstants.TAG_R_N);
		sql.append(comment);
		sql.append(StringConstants.TAG_R_N);
		sql.append(index);
		sql.append(StringConstants.TAG_R_N);
	}

	/**
	 * 基于MySQL类型的创建表DDL
	 *
	 * @param tableName
	 *            表明
	 * @param tableComment
	 *            表注释
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void tableDDLByMySQL(final String tableName, final String tableComment, final List<Field> fields,
			final StringBuffer sql) {
		//			DROP TABLE IF EXISTS USER_INFO;
		//
		//			/*=============================*/
		//			/* Table: USER_INFO            */
		//			/*=============================*/
		//			CREATE TABLE USER_INFO
		//			(
		//			   LOGIN_NAME           VARCHAR(100) NOT NULL COMMENT '登录名',
		//			   EXT1                 VARCHAR(100) DEFAULT '0',
		//			   PRIMARY KEY (LOGIN_NAME)
		//			);
		//
		//			ALTER TABLE USER_INFO COMMENT '用户信息表';
		//
		//			CREATE INDEX INX_USER_INFO_LOGIN_NAME ON USER_INFO (LOGIN_NAME) ;

		// 删除表
		sql.append("DROP TABLE IF EXISTS " + tableName + " ;" + StringConstants.TAG_R_N);
		// 表注释
		sql.append("/*===================================*/" + StringConstants.TAG_R_N);
		sql.append("/* TABLE: " + tableName + "                  */" + StringConstants.TAG_R_N);
		sql.append("/*===================================*/" + StringConstants.TAG_R_N);

		// 创建表
		sql.append("CREATE TABLE " + tableName + "  (" + StringConstants.TAG_R_N);
		// 主键
		Field primaryKey = null;
		// 序列
		final StringBuffer index = new StringBuffer("");
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			if (StringConstants.YES.equalsIgnoreCase(field.getDataIsPrimaryKey())) {
				primaryKey = field;
			}
			// 拼接表属性
			sql.append(" " + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			sql.append(" COMMENT '" + field.getDataName() + "'");
			sql.append(",");

			sql.append(StringConstants.TAG_R_N);
			if (StringConstants.YES.equalsIgnoreCase(field.getDataIsIndex())) {
				index.append("CREATE INDEX INX_" + tableName + "_" + field.getDataCode() + " ON " + tableName + " ("
						+ field.getDataCode() + ") ;");
				index.append(StringConstants.TAG_R_N);
			}
		}
		// 是否有主键
		if (primaryKey != null) {
			sql.append("PRIMARY KEY (" + primaryKey.getDataCode() + ")");
		} else {
			// 删除多余的
			sql.delete(sql.length() - ("," + StringConstants.TAG_R_N).length(), sql.length());
		}
		sql.append(StringConstants.TAG_R_N);
		sql.append(");");
		sql.append(StringConstants.TAG_R_N);
		sql.append("ALTER TABLE " + tableName + " COMMENT '" + tableComment + "';");
		sql.append(StringConstants.TAG_R_N);
		sql.append(index);
		sql.append(StringConstants.TAG_R_N);
	}

	/**
	 * 基于SQLServer类型的创建表DDL
	 *
	 * @param tableName
	 *            表明
	 * @param tableComment
	 *            表注释
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void tableDDLBySQLServer(final String tableName, final String tableComment, final List<Field> fields,
			final StringBuffer sql) {
		//		  IF EXISTS (SELECT 1
		//		            FROM  SYSOBJECTS
		//		           WHERE  ID = OBJECT_ID('USER_INFO')
		//		            AND   TYPE = 'U')
		//		   DROP TABLE USER_INFO
		//		   GO
		//
		//		   /*==========================*/
		//		   /* Table: USER_INFO         */
		//		   /*==========================*/
		//		   CREATE TABLE USER_INFO (
		//		      LOGIN_NAME           VARCHAR(100)			NOT NULL,
		//		      EXT1                 VARCHAR(100)   DEFAULT '0'      NULL,
		//		      CONSTRAINT PK_USER_INFO PRIMARY KEY  NONCLUSTERED (LOGIN_NAME)
		//		   )
		//		   GO
		//
		//		   DECLARE @CURRENTUSER SYSNAME
		//		   SELECT @CURRENTUSER = USER_NAME()
		//		   EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION',
		//		      '用户信息表',
		//		      'USER', @CURRENTUSER, 'TABLE', 'USER_INFO'
		//		   GO
		//
		//		   DECLARE @CURRENTUSER SYSNAME
		//		   SELECT @CURRENTUSER = USER_NAME()
		//		   EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION',
		//		      '登录名',
		//		      'USER', @CURRENTUSER, 'TABLE', 'USER_INFO', 'COLUMN', 'LOGIN_NAME'
		//		   GO
		//
		//		   CREATE INDEX INX_USER_INFO_LOGIN_NAME ON USER_INFO (LOGIN_NAME) ;
		//		   GO

		// 删除表
		sql.append("IF EXISTS (SELECT 1 " + StringConstants.TAG_R_N);
		sql.append("          FROM  SYSOBJECTS " + StringConstants.TAG_R_N);
		sql.append("        WHERE  ID = OBJECT_ID('" + tableName + "') " + StringConstants.TAG_R_N);
		sql.append("         AND   TYPE = 'U') " + StringConstants.TAG_R_N);
		sql.append("DROP TABLE " + tableName + ") " + StringConstants.TAG_R_N);
		sql.append("GO) " + StringConstants.TAG_R_N);

		// 表注释
		sql.append("/*===================================*/" + StringConstants.TAG_R_N);
		sql.append("/* TABLE: " + tableName + "                  */" + StringConstants.TAG_R_N);
		sql.append("/*===================================*/" + StringConstants.TAG_R_N);

		// 创建表
		sql.append("CREATE TABLE " + tableName + "  (" + StringConstants.TAG_R_N);
		// 主键
		Field primaryKey = null;
		// 列注释
		final StringBuffer comment = new StringBuffer("");
		// 序列
		final StringBuffer index = new StringBuffer("");
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			if (StringConstants.YES.equalsIgnoreCase(field.getDataIsPrimaryKey())) {
				primaryKey = field;
			}
			// 拼接表属性
			sql.append(" " + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			sql.append(",");

			sql.append(StringConstants.TAG_R_N);

			// 拼接列注释
			comment.append("DECLARE @CURRENTUSER SYSNAME" + StringConstants.TAG_R_N);
			comment.append("SELECT @CURRENTUSER = USER_NAME()" + StringConstants.TAG_R_N);
			comment.append("EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION', " + StringConstants.TAG_R_N);
			comment.append("'" + field.getDataName() + "', " + StringConstants.TAG_R_N);
			comment.append("'USER', @CURRENTUSER, 'TABLE', '" + tableName + "', 'COLUMN', '" + field.getDataCode()
					+ "' " + StringConstants.TAG_R_N);
			comment.append("GO" + StringConstants.TAG_R_N);

			if (StringConstants.YES.equalsIgnoreCase(field.getDataIsIndex())) {
				index.append("CREATE INDEX INX_" + tableName + "_" + field.getDataCode() + " ON " + tableName + " ("
						+ field.getDataCode() + ") ;");
				index.append("GO" + StringConstants.TAG_R_N);
			}

		}
		// 是否有主键
		if (primaryKey != null) {
			sql.append("CONSTRAINT PK_" + tableName + " PRIMARY KEY  NONCLUSTERED (" + primaryKey.getDataCode() + ")");
		} else {
			// 删除多余的
			sql.delete(sql.length() - ("," + StringConstants.TAG_R_N).length(), sql.length());
		}
		sql.append(StringConstants.TAG_R_N);
		sql.append(")");
		sql.append(StringConstants.TAG_R_N);
		sql.append("GO");

		sql.append(StringConstants.TAG_R_N);

		sql.append("DECLARE @CURRENTUSER SYSNAME" + StringConstants.TAG_R_N);
		sql.append("SELECT @CURRENTUSER = USER_NAME()" + StringConstants.TAG_R_N);
		sql.append("EXECUTE SP_ADDEXTENDEDPROPERTY 'MS_DESCRIPTION', " + StringConstants.TAG_R_N);
		sql.append("   '" + tableComment + "', " + StringConstants.TAG_R_N);
		sql.append("   'USER', @CURRENTUSER, 'TABLE', '" + tableName + "' " + StringConstants.TAG_R_N);
		sql.append("GO ");

		sql.append(StringConstants.TAG_R_N);
		sql.append(comment);
		sql.append(StringConstants.TAG_R_N);
		sql.append(index);
		sql.append(StringConstants.TAG_R_N);
	}

	/**
	 * 基于ORACLE类型的添加字段方法
	 *
	 * @param tableName
	 *            表明
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void alertAddFieldByOracle(final String tableName, final List<Field> fields, final StringBuffer sql) {
		setPrimarykey(tableName, sql, fields);
		//		sql.append(StringConstants.TAG_R_N);
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			//	ALTER TABLE USER_INFO ADD LOGIN_NAME VARCHAR(50) DEFAULT '0' NOT NULL;
			//	COMMENT ON COLUMN USER_INFO.LOGIN_NAME IS '登录名';
			sql.append("ALTER TABLE " + tableName + " ADD " + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			sql.append(";");
			sql.append(StringConstants.TAG_R_N);
			sql.append("COMMENT ON COLUMN " + tableName + "." + field.getDataCode() + " IS '" + field.getDataName()
					+ "';");
			sql.append(StringConstants.TAG_R_N);
		}
	}

	/**
	 * 基于MySQL类型的添加字段方法
	 *
	 * @param tableName
	 *            表明
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void alertAddFieldByMySQL(final String tableName, final List<Field> fields, final StringBuffer sql) {
		setPrimarykey(tableName, sql, fields);
		//		sql.append(StringConstants.TAG_R_N);
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			//	ALTER TABLE USER_INFO ADD (LOGIN_NAME VARCHAR(50) DEFAULT '0' NOT NULL COMMENT '登录名' );
			sql.append("ALTER TABLE " + tableName + " ADD (" + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			if (StringUtils.isNotEmpty(field.getDataName())) {
				sql.append(" COMMENT '" + field.getDataName() + "'");
			}
			sql.append(" );");
			sql.append(StringConstants.TAG_R_N);
		}
	}

	/**
	 * 基于SQLServer类型的添加字段方法
	 *
	 * @param tableName
	 *            表明
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void alertAddFieldBySQLServer(final String tableName, final List<Field> fields, final StringBuffer sql) {
		setPrimarykey(tableName, sql, fields);
		//		sql.append(StringConstants.TAG_R_N);
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			//	ALTER TABLE USER_INFO ADD USER_NAME VARCHAR(10) DEFAULT '0' NOT NULL PRIMARY KEY (`aa`);
			//	declare @CurrentUser sysname
			//	select @CurrentUser = user_name()
			//	execute sp_addextendedproperty 'MS_Description',
			//	   '用户名',
			//	  'user', @CurrentUser, 'table', 'USER_INFO', 'column', 'USER_NAME'
			//	go
			sql.append("ALTER TABLE " + tableName + " ADD " + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			sql.append(";");
			sql.append(StringConstants.TAG_R_N);

			sql.append("declare @CurrentUser sysname");
			sql.append("select @CurrentUser = user_name()");
			sql.append("execute sp_addextendedproperty 'MS_Description', ");
			sql.append("'" + field.getDataName() + "', ");
			sql.append("'user', @CurrentUser, 'table', '" + tableName + "', 'column', '" + field.getDataCode() + "' ");
			sql.append("go");
			sql.append(StringConstants.TAG_R_N);
		}
	}

	/**
	 * 基于ORACLE类型的修改字段方法
	 *
	 * @param tableName
	 *            表明
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void alertModifyFieldByOracle(final String tableName, final List<Field> fields, final StringBuffer sql) {
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			//	ALTER TABLE USER_INFO MODIFY LOGIN_NAME VARCHAR(50) DEFAULT '0' NOT NULL;
			//	COMMENT ON COLUMN USER_INFO.LOGIN_NAME IS '登录名';
			sql.append("ALTER TABLE " + tableName + " MODIFY " + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			sql.append(";");
			sql.append(StringConstants.TAG_R_N);
			sql.append("COMMENT ON COLUMN " + tableName + "." + field.getDataCode() + " IS '" + field.getDataName()
					+ "';");
			sql.append(StringConstants.TAG_R_N);
		}
	}

	/**
	 * 基于MySQL类型的修改字段方法
	 *
	 * @param tableName
	 *            表明
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void alertModifyFieldByMySQL(final String tableName, final List<Field> fields, final StringBuffer sql) {
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			//	ALTER TABLE USER_INFO MODIFY LOGIN_NAME VARCHAR(50) DEFAULT '0' NOT NULL COMMENT '登录名';
			sql.append("ALTER TABLE " + tableName + " MODIFY " + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			if (StringUtils.isNotEmpty(field.getDataName())) {
				sql.append(" COMMENT '" + field.getDataName() + "'");
			}
			sql.append(" ;");
			sql.append(StringConstants.TAG_R_N);
		}
	}

	/**
	 * 基于SQLServer类型的修改字段方法
	 *
	 * @param tableName
	 *            表明
	 * @param fields
	 *            列
	 * @param sql
	 *            语句
	 */
	private void alertModifyFieldBySQLServer(final String tableName, final List<Field> fields, final StringBuffer sql) {
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			//	ALTER TABLE USER_INFO ALTER COLUMN USER_NAME VARCHAR(10) DEFAULT '0' NOT NULL;
			//	declare @CurrentUser sysname
			//	select @CurrentUser = user_name()
			//	execute sp_addextendedproperty 'MS_Description',
			//	   '用户名',
			//	  'user', @CurrentUser, 'table', 'USER_INFO', 'column', 'USER_NAME'
			//	go
			sql.append("ALTER TABLE " + tableName + " ALTER COLUMN " + field.getDataCode() + " " + field.getDataType());

			setLength(sql, field);
			setDefault(sql, field);
			setIsNull(sql, field);
			sql.append(";");
			sql.append(StringConstants.TAG_R_N);

			sql.append("declare @CurrentUser sysname");
			sql.append("select @CurrentUser = user_name()");
			sql.append("execute sp_addextendedproperty 'MS_Description', ");
			sql.append("'" + field.getDataName() + "', ");
			sql.append("'user', @CurrentUser, 'table', '" + tableName + "', 'column', '" + field.getDataCode() + "' ");
			sql.append("go");
			sql.append(StringConstants.TAG_R_N);
		}
	}

	/**
	 * 设置是否允许为空
	 *
	 * @param sql
	 *            SQL语句
	 * @param field
	 *            字段
	 */
	private void setIsNull(final StringBuffer sql, final Field field) {
		if (StringConstants.NO.equals(field.getDataIsNull())) {
			sql.append(" NOT NULL ");
		} else if (StringConstants.YES.equals(field.getDataIsNull())) {
			//			sql.append(" NULL ");
		}
	}

	/**
	 * 设置主键
	 *
	 * @param tableName
	 *            表名
	 * @param sql
	 *            SQL语句
	 * @param fields
	 *            字段
	 * @param field
	 *            字段
	 */
	private void setPrimarykey(final String tableName, final StringBuffer sql, final List<Field> fields) {
		boolean hasPriKey = false;

		final StringBuffer sqlTemp = new StringBuffer();
		for (final Field field : fields) {
			if (StringUtils.isEmpty(field.getDataType())) {
				continue;
			}
			if (StringConstants.YES.equals(field.getDataIsPrimaryKey())) {
				hasPriKey = true;
				sqlTemp.append(field.getDataCode());
				sqlTemp.append(",");
			}
		}

		if (hasPriKey) {
			sqlTemp.deleteCharAt(sqlTemp.length() - 1);
			//	ALTER TABLE PHONE DROP PRIMARY KEY;
			sql.append("ALTER TABLE " + tableName + " DROP PRIMARY KEY;");
			sql.append(StringConstants.TAG_R_N);
			//	ALTER TABLE USER_INFO ADD PRIMARY KEY(AA,BB);
			sql.append("ALTER TABLE " + tableName + " ADD PRIMARY KEY (");
			sql.append(sqlTemp);
			sql.append(");");
		}
	}

	/**
	 * 设置默认值
	 *
	 * @param sql
	 *            SQL语句
	 * @param field
	 *            字段
	 */
	private void setDefault(final StringBuffer sql, final Field field) {
		field.setDataDefault(StringUtils.trimToEmpty(field.getDataDefault()));
		if (StringUtils.isEmpty(field.getDataDefault())) {
			return;
		}

		// 判断类型，是否加单引号
		if ("null".equalsIgnoreCase(field.getDataDefault())) {
			sql.append(" DEFAULT NULL ");
		} else if ("string".equalsIgnoreCase(SQLUtil.dbFieldToJava(field.getDataType()))) {
			sql.append(" DEFAULT '" + field.getDataDefault() + "' ");
		} else {
			sql.append(" DEFAULT " + field.getDataDefault() + " ");
		}
	}

	/**
	 * 设置字段大小
	 *
	 * @param sql
	 *            SQL语句
	 * @param field
	 *            字段
	 */
	private void setLength(final StringBuffer sql, final Field field) {
		if (isLength(field.getDataType())) {
			sql.append(" (" + field.getDataLength());
			if (field.getDataPrecision() >= 0) {
				sql.append("," + field.getDataPrecision() + ") ");
			} else {
				sql.append(") ");
			}
		}
	}

	private static final String[] noLength = { "date", "datetime", "time", "blob", "clob", "text", "longtext" };

	/**
	 * 是否有长度属性
	 * <p>
	 * 兼容多数据库
	 *
	 * @param dataType
	 *            字段类型
	 * @return true是
	 */
	private boolean isLength(final String dataType) {
		if (StringUtils.isEmpty(dataType)) {
			return false;
		}
		boolean result = true;
		for (final String str : TableUtil.noLength) {
			if (str.equalsIgnoreCase(dataType)) {
				result = false;
				break;
			}
		}
		return result;
	}
}
